-- 创建数据库语句 -- 创建数据库并指定中文字符集
CREATE DATABASE SPIDER_DB
ON
(
    NAME = SPIDER_DB,
    FILENAME = '/var/opt/mssql/SPIDER_DB.mdf',
    SIZE = 5,
    MAXSIZE = 20400
)
LOG ON
(
    NAME = SPIDER_DB_LOG,
    FILENAME = '/var/opt/mssql/SPIDER_DB_LOG.ldf',
    SIZE = 64,
    MAXSIZE = 512
)
COLLATE Chinese_PRC_CI_AS

IF SERVERPROPERTY('ProductVersion') > '12'
BEGIN
    ALTER DATABASE [SPIDER_DB] SET QUERY_STORE=ON;
END
GO

-- 创建数据表语句
CREATE TABLE dbo.TEST_TABLE
(
    [ID] BIGINT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    [NAME] NVARCHAR(128),
    [AGE] SMALLINT,
)

-- 插入语句
INSERT INTO dbo.TEST_TABLE
([NAME], [AGE])
VALUES
('张国强',  26),
('李长安',  27)

-- 创建使用用户id将信息输出成JSON的存储过程
IF OBJECT_ID('dbo.U_SP_GET_USER_INFO', 'P') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.U_SP_GET_USER_INFO
END
GO

CREATE PROCEDURE dbo.U_SP_GET_USER_INFO
	@id INT
AS
BEGIN
   --Insert statements for the stored procedure here
 SELECT [ID]
       ,[NAME] 
       ,[AGE] 
   FROM dbo.TEST_TABLE
  WHERE [ID] = @id
    FOR JSON PATH
END
GO

EXECUTE dbo.U_SP_GET_USER_INFO 1
GO

-- 创建使用JSON数据来创建用户信息的存储过程
IF OBJECT_ID('dbo.U_SP_SET_USER_INFO', 'P') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.U_SP_SET_USER_INFO
END
GO

CREATE PROCEDURE dbo.U_SP_SET_USER_INFO
    @json_val NVARCHAR(MAX) 
AS
    -- body of the stored procedure
    INSERT INTO dbo.TEST_TABLE ([NAME], [AGE])
    SELECT [NAME], [AGE]
      FROM OPENJSON (@json_val)
      WITH(
            NAME nvarchar(512), 
            AGE SMALLINT
    )
GO

-- 测试 USP_SET_USER_INFO
declare @json nvarchar(max) =
N'[
    {
        "NAME": "刘天云",
        "AGE": 23
    }
]'

EXECUTE dbo.U_SP_SET_USER_INFO @json_val = @json
EXECUTE dbo.U_SP_GET_USER_INFO 4